package com.szholly.plug.form.excel.utils;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

import com.szholly.plug.form.excel.model.ColumnProperty;

/**
 * Java操作Excel封装
 */
@SuppressWarnings("deprecation")
public class ExcelUtil {
	public static final Integer LIST_TYPE = 0;
	public static final Integer CARD_TYPE = 1;

	/**
	 * 导入Excel文件 内容以List<Map<String K,String V>>的方式存放
	 * 
	 * @param excelFile
	 *            : Excel文件对象
	 * @param arrColumn
	 *            : Map的Key列表，Value为相应的sheet一行中各列的值
	 * @return
	 */
	public List<Map<String, String>> importExcelToMap(String excelFile,
			ReaderXmSAX readXml) throws Exception {
		int iDataIndex = readXml.getDataIndex();
		int iType = readXml.getType();
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile));
		List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
		List<String> arrColumn = readXml.getTableEntitys().get(0)
				.getColumnName();
		int i = 1;
		try {
			// 获取Excel数据开始
			HSSFWorkbook workbook = new HSSFWorkbook(fs);
			List<ColumnProperty> listCol = readXml.getTableEntitys().get(0)
					.getColumnPropertys();
			Map<String, String> cells = new HashMap<String, String>();
			for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) {
				HSSFSheet sheet = workbook.getSheetAt(sheetCount);
				int rows = sheet.getPhysicalNumberOfRows();
				// 解析配置文件中数据行 读取Excel数据开始
				if (LIST_TYPE == iType) {
					for (int rowCount = iDataIndex - 1; rowCount < rows; rowCount++, i++) {
						HSSFRow sourceRow = sheet.getRow(rowCount);
						if (sourceRow == null)
							break;
						listMap.add(getRowData(sourceRow, arrColumn));
					}
				} else {
					HSSFRow sourceRow = null;
					for (ColumnProperty col : listCol) {
						int sheetNum = col.getSheet();
						if (sheetNum == sheetCount) {
							sourceRow = sheet.getRow(col.getRowPosition());
							if (sourceRow == null)
								break;
							String strCellValue = "";
							if (col.getRowPosition() != 0
									&& col.getCellPosition() != 0) {
								int j = isMergedRegion(sheet,
										col.getRowPosition(),
										col.getCellPosition());
								if (j >= 0) {
									strCellValue = getMergedReginValue(sheet, j);
								} else {
									strCellValue = getCellValue(sourceRow
											.getCell((short) col
													.getCellPosition()));
								}
							}
							cells.put(col.getColumnName(), strCellValue);
						}
					}
				}
			}
			if (CARD_TYPE == iType) {
				listMap.add(cells);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("导入中断，错误位置：第" + i + "行数据！");
		}
		return listMap;
	}

	/**
	 * 获取行数据
	 * 
	 * @param sheet
	 * @param strKey
	 * @param rowIndex
	 * @return
	 */
	public Map<String, String> getRowData(HSSFRow sheetRow,
			List<String> arrColumn) {
		Map<String, String> cells = new HashMap<String, String>();
		int i = sheetRow.getFirstCellNum();
		while (i < sheetRow.getLastCellNum()) {
			HSSFCell cell = sheetRow.getCell((short) i++);
			cells.put(arrColumn.get(i - 1), getCellValue(cell));
		}
		return cells;
	}

	public static String getCellValue(HSSFCell cell) {
		String strCellValue = "";
		if (cell == null) {
			strCellValue = "";
		} else {
			Object val = null;
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_BOOLEAN:
				val = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_FORMULA:
				val = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat dateFormat = new SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss");
					val = dateFormat.format((cell.getDateCellValue()));
				} else {
					if (cell.getNumericCellValue() > (long) cell
							.getNumericCellValue()) {
						val = String.valueOf((double) cell
								.getNumericCellValue());
					} else {
						val = String.valueOf((long) cell.getNumericCellValue());
					}
				}
				break;
			case Cell.CELL_TYPE_STRING:
				val = cell.getRichStringCellValue();
				break;
			default:
				val = cell.getRichStringCellValue();
			}
			strCellValue = String.valueOf(val);
		}
		return strCellValue;
	}

	/**
	 * * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return j 合并单元格的位置
	 * */
	public int isMergedRegion(HSSFSheet sheet, int row, int column) {
		int num = sheet.getNumMergedRegions();
		for (int j = 0; j < num; j++) {
			Region reg = sheet.getMergedRegionAt(j);
			int firstColumn = reg.getColumnFrom();
			int lastColumn = reg.getColumnTo();
			int firstRow = reg.getRowFrom();
			int lastRow = reg.getRowTo();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return j;
				}
			}
		}
		return -1;
	}

	public static String getMergedReginValue(HSSFSheet sheet, int j) {
		Region reg = sheet.getMergedRegionAt(j);
		HSSFCell cell = sheet.getRow(reg.getRowFrom()).getCell(
				reg.getColumnFrom());
		return getCellValue(cell);
	}

	public static HSSFCell getMergedReginCell(HSSFSheet sheet, int j) {
		Region reg = sheet.getMergedRegionAt(j);
		HSSFCell cell = sheet.getRow(reg.getRowFrom()).getCell(
				reg.getColumnFrom());
		return cell;
	}

	public static String exportExcelByMap(List<Map<String, String>> exportMaps,
			String templateFilePath, String templateFilename) throws Exception {
		return exportExcel(exportMaps, templateFilePath, templateFilename);
	}

	/**
	 * 导出Excel文件,根据指定路径下的模板生成输出的Excel文件
	 * 
	 * @param exportObjects
	 *            待导出的对象数组
	 * @param exportInfo
	 *            模板文件的其他附加信息(非结果集内容)
	 * @param templateFilename
	 *            模板文件名(不带扩展名),对应到在user-config.xml配置路径下的模板文件
	 * @param autoPagination
	 *            是否分页
	 * @param autoSheet
	 *            是否分工作表
	 * @param ApplicationContext
	 * @return 返回生成的Excel文件下载路径
	 * @throws Exception
	 */
	private static String exportExcel(List<Map<String, String>> exportObjects,
			String templateFilePath, String templateFilename) throws Exception {
		String filename = templateFilename;
		if (filename.indexOf(".xls") == -1) {
			filename += ".xls";
		}
		// 临时路径是服务器当前war下面的excel-config目录
		String templateDir = templateFilePath;
		// String templateDir =
		// ApplicationContext.getInstance().getWarRealPath() +
		// "/WEB-INF/excel-config";
		System.out.println("临时路径是服务器当前war下面的excel-config目录" + templateDir);

		if (!templateDir.endsWith("/")) {
			templateDir += "/";
		}
		String tempDir = templateDir + "temp/";
		File file = new File(tempDir);
		if (!file.exists()) {
			// 创建临时目录
			file.createNewFile();
		}
		String templateFile = templateDir + filename;
		String outputFile = tempDir + generateOutputExcelFile(filename);// 路径+名称
		ExcelTemplate template = new ExcelTemplate(templateFile, outputFile);
		int excelExportMaxnumInt = 0;
		try {
			excelExportMaxnumInt = 10000;
		} catch (Exception e) {
			e.printStackTrace();
		}
		template.setMaxRow(excelExportMaxnumInt);
		template.generate(exportObjects, null);
		return outputFile;
	}

	/**
	 * 生成EXCEL输出文件，默认带时间戳
	 * 
	 * @param templateFilename
	 *            文件名
	 * @return
	 */
	private static String generateOutputExcelFile(String templateFilename) {
		String filename = templateFilename;
		if (templateFilename.endsWith(".xls")) {
			filename = templateFilename.substring(0,
					templateFilename.length() - 4);
		}
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
		String datetimeString = format.format(new Date());
		filename = filename + "_" + datetimeString + ".xls";
		return filename;
	}
}